package com.edu.oracle.dao;

import com.edu.oracle.pojo.Course;
import com.edu.oracle.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CourseDao {
    public List<Course> list() {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            //第一步和第二步jdbc操作步骤
            connection = JdbcUtils.getConnection();

            //3.创建执行对象
            String sql="select * from course";
            preparedStatement = connection.prepareStatement(sql);
            resultSet= preparedStatement.executeQuery();

            //4.处理结果集
            //4.1集合存储
            List<Course> courses = new ArrayList<>();
            while (resultSet.next()){
                Course course = new Course();//实体类按住列存储，单位较小
                course.setCno(resultSet.getString("cno"));
                course.setCname(resultSet.getString("cname"));
                course.setCredit(resultSet.getInt("credit"));
                course.setTno(resultSet.getString("tno"));
                courses.add(course);//list集合按对象存储
            }
            //4.2返回结果集
            return courses;


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }
        return null;
    }

    public boolean delete(String cno) {


        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            //第一步和第二步jdbc操作步骤
            connection = JdbcUtils.getConnection();

            //3.创建执行对象
            String sql="delete from course where cno=?";//
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cno);
            preparedStatement.executeUpdate();

            //4.处理结果集
            //4.1集合存储
            //4.2返回结果集
            return true;


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }

        return false;
    }

    public Boolean add(Course course) {

        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            //第一步和第二步jdbc操作步骤
            connection = JdbcUtils.getConnection();

            //3.创建执行对象
            String sql="insert into course (cno,cname,credit) values (?,?,?)";//
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,course.getCno());
            preparedStatement.setString(2,course.getCname());
            preparedStatement.setInt(3,course.getCredit());
            //preparedStatement.setString(4,course.getTno());

            preparedStatement.executeUpdate();

            //4.处理结果集
            return true;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }

        return false;
    }

    public Course selectByOne(String cno) {

        //赋值listCourse里面的内容，修改
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            //第一步和第二步jdbc操作步骤
            connection = JdbcUtils.getConnection();

            //3.创建执行对象
            String sql="select * from course where cno=?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,cno);

            resultSet= preparedStatement.executeQuery();

            //4.处理结果集

            Course course = new Course();//实体类按住列存储，单位较小
            while (resultSet.next()){
                course.setCno(resultSet.getString("cno"));
                course.setCname(resultSet.getString("cname"));
                course.setCredit(resultSet.getInt("credit"));
                course.setTno(resultSet.getString("tno"));

                //list集合按对象存储
            }
            //4.2返回结果集
            return course;


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }

        return null;
    }

    public boolean update(Course course) {

        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            //第一步和第二步jdbc操作步骤
            connection = JdbcUtils.getConnection();

            //3.创建执行对象
            String sql="update course set cname=?,credit=? where cno=?";//
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(3,course.getCno());
            preparedStatement.setString(1,course.getCname());
            preparedStatement.setInt(2,course.getCredit());
            //preparedStatement.setString(4,course.getTno());

            preparedStatement.executeUpdate();

            //4.处理结果集
            return true;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5.关闭资源
            JdbcUtils.closeResoure(connection,preparedStatement,resultSet);
        }

        return  false;
    }
}
